﻿using Microsoft.Reporting.WinForms;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace CMSWindowsFormsApp2
{
    public partial class JobReportForm : Form
    {
        public JobReportForm()
        {
            InitializeComponent();
        }

        private void JobReportForm_Load(object sender, EventArgs e)
        {

            DataTable dt = GetConsumableData();
            ReportDataSource rds = new ReportDataSource("JobDataSet1",dt);

            this.reportViewer1.LocalReport.DataSources.Clear();
            this.reportViewer1.LocalReport.DataSources.Add(rds);
            this.reportViewer1.RefreshReport();
        }

        private DataTable GetConsumableData()
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn=new SqlConnection("Data Source=DESKTOP-SR1MKQK;Initial Catalog=CMSDATABASE;Integrated Security=True")) {
                string str = "SELECT FORMAT(JobDate, 'MM/yyyy') AS JobDate,SUM(Tires) AS Tires, SUM(Weights) AS Weights,SUM(OilChanged) AS OilChanged,SUM(OilFilter) AS OilFilter,SUM(GearOil) AS GearOil ,SUM(Point) as Point,SUM(Condenser) as Condenser,SUM(Plug) as Plug,SUM(FuelFilter) as FuelFilter,SUM(AirFilter) as AirFilter FROM tblJobDetails WHERE JobDate >= DATEFROMPARTS(YEAR(GETDATE()), 1, 1) AND  JobDate<DATEADD(YEAR, 1, DATEFROMPARTS(YEAR(GETDATE()), 1, 1)) GROUP BY FORMAT(JobDate, 'MM/yyyy') ORDER BY JobDate";
                SqlDataAdapter d = new SqlDataAdapter(str,conn);
                d.Fill(dt);
            }
            return dt;
        }

    }
}
